﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using You2V.Data.Shared;
using You2V.Data;
using System.Data;

namespace You2V.LocalDBLayer
{
    public class StudentSponsorDBL
    {
        public StudentSponsorDBL()
        {
        }

        // chg 20120825 为了防止冲突，不再用static
        //public static StudentSponsorDBL Instance = new StudentSponsorDBL();

        #region 读取学生-资助人 1：1关系

        public List<Student_vs_Sponsor> GetStudent_vs_SponsorByKeyword(string keyWord)
        {
            string strWhere = " 1=1 ";
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                strWhere = " tc_nam like '%" + keyWord + "%' "
                    + "OR tc_call like '%" + keyWord + "%' "
                    + "OR tc_mail like '%" + keyWord + "%' "
                    + "OR tc_tel like '%" + keyWord + "%' ";
            }

            return GetStudent_vs_SponsorGivenWhereCondition(strWhere);
        }

        public List<Student_vs_Sponsor> GetStudent_vs_SponsorGivenWhereCondition()
        {
            return GetStudent_vs_SponsorGivenWhereCondition(" 1=1 ");
        }

        public List<Student_vs_Sponsor> GetStudent_vs_SponsorGivenWhereCondition(string strWhere)
        {
            if (string.IsNullOrEmpty(strWhere))
                strWhere = " 1=1 ";

            List<Student_vs_Sponsor> ResultList = new List<Student_vs_Sponsor>();

            string sqlQuery = "SELECT im.*, Stu.ID, Stu.StudentNumber,Stu.FullName,Stu.Gender,Stu.SchoolShortName,Stu.ClassName "
                                + "FROM Student2012 Stu,im WHERE Stu.tc_id = im.tc_Studentid AND (" + strWhere + ")";

            DataTable dt = DBAccess.GetDataTable(sqlQuery);

            if (dt == null)
            {
                return null;
            }

            foreach (DataRow row in dt.Rows)
            {
                Student_vs_Sponsor item = new Student_vs_Sponsor();

                item.Student.ID = (Guid)row["ID"];
                item.Student.StudentNumber = (string)row["StudentNumber"];
                item.Student.FullName = (string)row["FullName"];
                item.Student.Gender = (string)row["Gender"];
                
                if (item.Student.SchoolInfo == null)
                    item.Student.SchoolInfo = new SchoolInfo();

                item.Student.SchoolInfo.SchoolShortName = (string)row["SchoolShortName"];
                item.Student.ClassName = (string)row["ClassName"];

                item.Sponsor.ShortName = (string)row["tc_nam"];
                item.Sponsor.Name = (string)row["tc_call"];
                item.Sponsor.Title = (string)row["tc_ccl"];
                item.Sponsor.Email = (string)row["tc_mail"];
                item.Sponsor.Postcode = (string)row["tc_post"];
                item.Sponsor.Address = (string)row["tc_dz"];
                item.Sponsor.Telephone = (string)row["tc_tel"];
                item.Sponsor.PayPeriod = (string)row["tc_pay"];
                item.Sponsor.PayMethod = (string)row["tc_meo"];
                item.Sponsor.SponsorMessage = (string)row["tc_bz"];
                item.Sponsor.CreateDate = (DateTime)row["tc_date"];

                ResultList.Add(item);
            }

            return ResultList;
        }
        #endregion
    }
}
